package com.redmoon.oa.chart;

import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultRecord;
import cn.js.fan.db.SQLFilter;
import cn.js.fan.util.DateUtil;
import cn.js.fan.util.ErrMsgException;
import cn.js.fan.util.ParamUtil;
import cn.js.fan.util.StrUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cloudweb.oa.api.IBasicSelectCtl;
import com.cloudweb.oa.service.MacroCtlService;
import com.cloudweb.oa.utils.SpringUtil;
import com.cloudwebsoft.framework.db.JdbcTemplate;
import com.redmoon.oa.basic.SelectDb;
import com.redmoon.oa.basic.SelectOptionDb;
import com.redmoon.oa.dept.DeptDb;
import com.redmoon.oa.dept.DeptUserDb;
import com.redmoon.oa.flow.FormDb;
import com.redmoon.oa.flow.FormField;
import com.redmoon.oa.flow.FormParser;
import com.redmoon.oa.person.UserDb;
import com.redmoon.oa.pvg.Privilege;
import com.redmoon.oa.pvg.RoleDb;
import com.redmoon.oa.shell.BSHShell;
import com.redmoon.oa.sys.DebugUtil;
import com.redmoon.oa.visual.FormDAO;
import com.redmoon.oa.visual.ModuleSetupDb;
import com.redmoon.oa.visual.ModuleUtil;
import com.redmoon.oa.visual.SQLBuilder;
import nl.bitwalker.useragentutils.Browser;

import javax.servlet.http.HttpServletRequest;
import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ChartUtil {

    public static String CONFIG_CHART = "config_chart";

    public static String generateCountSql(String sql, FormField ff, String value) {
        int p = sql.lastIndexOf(" order by ");
        sql = sql.substring(0, p);
        if (!sql.contains(" where ")) {
            if (FormField.isNumeric(ff.getFieldType())) {
                sql += " and " + ff.getName() + "=" + value;
            } else {
                sql += " where " + ff.getName() + "=" + StrUtil.sqlstr(value);
            }
        } else {
            if (FormField.isNumeric(ff.getFieldType())) {
                sql += " and " + ff.getName() + "=" + value;
            } else {
                sql += " and " + ff.getName() + "=" + StrUtil.sqlstr(value);
            }
        }
        return SQLFilter.getCountSql(sql);
    }

    public static com.alibaba.fastjson.JSONArray getDataForChart(HttpServletRequest request, ModuleSetupDb msd, FormDb fd, FormField ff) throws ErrMsgException {
        Privilege privilege = new Privilege();
        String userName = privilege.getUser(request);

        String orderBy = "", sort = "";
        String filter = StrUtil.getNullStr(msd.getFilter(userName)).trim();
        boolean isComb = filter.startsWith("<items>") || "".equals(filter);
        // 如果是组合条件，则赋予后台设置的排序字段
        if (isComb) {
            orderBy = StrUtil.getNullStr(msd.getString("orderby"));
            sort = StrUtil.getNullStr(msd.getString("sort"));
        }
        if ("".equals(orderBy)) {
            orderBy = "id";
        }

        if ("".equals(sort)) {
            sort = "desc";
        }

        String[] ary = null;
        try {
            request.setAttribute(ModuleUtil.MODULE_SETUP, msd);
            ary = SQLBuilder.getModuleListSqlAndUrlStr(request, fd, "", orderBy, sort);
        } catch (ErrMsgException e) {
            DebugUtil.e(ModuleUtil.class, "getModuleListSqlAndUrlStr", "SQL：" + e.getMessage());
            e.printStackTrace();
        }

        String sql = ary[0];
        String[][] arrOpts = null;
        if (ff.getType().equals(FormField.TYPE_SELECT)) {
            arrOpts = FormParser.getOptionsArrayOfSelect(fd, ff);
        } else if ("macro_flow_select".equals(ff.getMacroType())) {
            MacroCtlService macroCtlService = SpringUtil.getBean(MacroCtlService.class);
            IBasicSelectCtl basicSelectCtl = macroCtlService.getBasicSelectCtl();
            String basicCode = basicSelectCtl.getCode(ff);

            SelectDb sd = new SelectDb();
            sd = sd.getSelectDb(basicCode);
            Vector<SelectOptionDb> v = sd.getOptions();
            arrOpts = new String[v.size()][2];
            int i = 0;
            for (SelectOptionDb sod : v) {
                arrOpts[i][0] = sod.getValue();
                arrOpts[i][1] = sod.getName();
                i++;
            }
        }
        if (arrOpts == null) {
            throw new ErrMsgException("字段：" + ff.getTitle() + " 的选项为空");
        }

        com.alibaba.fastjson.JSONArray arr = new com.alibaba.fastjson.JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            for (String[] arrOpt : arrOpts) {
                String sqlOpt = generateCountSql(sql, ff, arrOpt[0]);
                ResultIterator ri = jt.executeQuery(sqlOpt);
                if (ri.hasNext()) {
                    ResultRecord rr = (ResultRecord) ri.next();
                    long count = rr.getLong(1);

                    com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
                    json.put("name", arrOpt[1]);
                    json.put("value", count);
                    arr.add(json);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jt.close();
        }
        return arr;
    }

    public static int getCountForChart(HttpServletRequest request, ModuleSetupDb msd) {
        FormDb fd = new FormDb();
        fd = fd.getFormDb(msd.getString("form_code"));
        try {
            request.setAttribute(ModuleUtil.MODULE_SETUP, msd);
            String sql = SQLBuilder.getModuleListSqlAndUrlStr(request, fd, "", "", "")[0];
            sql = SQLFilter.getCountSql(sql);
            JdbcTemplate jt = new JdbcTemplate();
            ResultIterator ri = jt.executeQuery(SQLFilter.getCountSql(sql));
            if (ri.hasNext()) {
                ResultRecord rr = ri.next();
                return rr.getInt(1);
            }
        } catch (ErrMsgException | SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static String parseField(FormDAO fdaoChart, String sql) {
        // 从request取得过滤条件中的参数值，组装为url字符串，以便于分页
        StringBuffer urlStrBuf = new StringBuffer();

        Pattern p = Pattern.compile(
                "\\{\\$([@A-Z0-9a-z-_\\u4e00-\\u9fa5\\xa1-\\xff\\.]+)\\}", // 前为utf8中文范围，后为gb2312中文范围
                Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String fieldName = m.group(1);
            String val = fdaoChart.getFieldValue(fieldName);
            m.appendReplacement(sb, val);
        }
        m.appendTail(sb);

        // {#fieldName}，展开后不带有单引号
        p = Pattern.compile(
                "\\{#([@A-Z0-9a-z-_\\u4e00-\\u9fa5\\xa1-\\xff\\.]+)\\}", // 前为utf8中文范围，后为gb2312中文范围
                Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
        m = p.matcher(sb.toString());
        sb = new StringBuffer();
        while (m.find()) {
            String fieldName = m.group(1);
            String val = fdaoChart.getFieldValue(fieldName);
            m.appendReplacement(sb, val);
        }
        m.appendTail(sb);
        return sb.toString();
    }

    public static Radar getDataForRadar(HttpServletRequest request, FormDAO fdaoChart) {
        String sqlIndicator = fdaoChart.getFieldValue("indicator"); // 如果语句中含有两个域，则认为第一个是文字，第二个是最大值
        long indicatorMax = StrUtil.toLong(fdaoChart.getFieldValue("indicator_max"), -1);
        int indicatorDlt = StrUtil.toInt(fdaoChart.getFieldValue("indicator_dlt"), 0);
        String radarShape = fdaoChart.getFieldValue("radar_shape");

        String sqlData1 = fdaoChart.getFieldValue("data1");
        String sqlData2 = fdaoChart.getFieldValue("data2");

        sqlIndicator = parseField(fdaoChart, sqlIndicator);
        sqlData1 = parseField(fdaoChart, sqlData1);
        if (!"".equals(sqlData2)) {
            sqlData2 = parseField(fdaoChart, sqlData2);
        }

        Radar radar = new Radar();
        int digit = StrUtil.toInt(fdaoChart.getFieldValue("digit"), 0);
        radar.setDigit(digit);
        radar.setShape(radarShape);

        com.alibaba.fastjson.JSONArray arrIndicator = new com.alibaba.fastjson.JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            if (indicatorMax == -1) {
                String sqlMax = parseField(fdaoChart, fdaoChart.getFieldValue("indicator_max"));
                ResultIterator ri = jt.executeQuery(sqlMax);
                if (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    indicatorMax = rr.getLong(1);
                }
            }
            indicatorMax += indicatorDlt;

            ResultIterator ri = jt.executeQuery(sqlIndicator);
            // 如果返回值只有一行一列
            if (ri.size() == 1) {
                ResultRecord rr = ri.next();
                String v = rr.getString(1);
                com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
                if (rr.getRow().size() == 1) { // 只有一列
                    json.put("name", v);
                    json.put("max", indicatorMax);
                } else {
                    json.put("name", v);
                    json.put("max", rr.getInt(2));
                }
                arrIndicator.add(json);
            } else if (ri.size() > 1) {
                while (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    String v = rr.getString(1);
                    com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
                    if (rr.getRow().size() == 1) {
                        json.put("name", v);
                        json.put("max", indicatorMax);
                    } else {
                        json.put("name", v);
                        json.put("max", rr.getInt(2));
                    }
                    arrIndicator.add(json);
                }
            }
            radar.setArrIndicator(arrIndicator);

            List<Double> data1 = new ArrayList<>();
            ri = jt.executeQuery(sqlData1);
            while (ri.hasNext()) {
                ResultRecord rr = ri.next();
                data1.add(rr.getDouble(1));
            }
            radar.setData1(data1);

            if (!"".equals(sqlData2)) {
                List<Double> data2 = new ArrayList<>();
                ri = jt.executeQuery(sqlData2);
                while (ri.hasNext()) {
                    ResultRecord rr = ri.next();
                    data2.add(rr.getDouble(1));
                }
                radar.setData2(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jt.close();
        }
        return radar;
    }

    public static Line getDataForLine(HttpServletRequest request, FormDAO fdaoChart) throws ErrMsgException {
        Line line = new Line();
        String sqlData1 = fdaoChart.getFieldValue("data1");
        // 如果data1为空，则为简单数据，取模块字段对应的count值
        if ("".equals(sqlData1)) {
            String moduleCode = fdaoChart.getFieldValue("module_code");
            String fieldName = fdaoChart.getFieldValue("field_name");

            ModuleSetupDb msd = new ModuleSetupDb();
            msd = msd.getModuleSetupDb(moduleCode);
            if (msd == null) {
                DebugUtil.e(ChartUtil.class, "getModuleSetupDb", "模块：" + moduleCode + "不存在！");
                throw new ErrMsgException("模块：" + moduleCode + "不存在！");
            }
            String formCode = msd.getString("form_code");

            FormDb fd = new FormDb();
            fd = fd.getFormDb(formCode);
            FormField ff = fd.getFormField(fieldName);

            JSONArray arr = ChartUtil.getDataForChart(request, msd, fd, ff);
            line.setSimpleData(true);
            line.setData(arr);
            return line;
        }

        String sqlData2 = fdaoChart.getFieldValue("data2");
        sqlData1 = parseField(fdaoChart, sqlData1);
        if (!"".equals(sqlData2)) {
            sqlData2 = parseField(fdaoChart, sqlData2);
        }

        int digit = StrUtil.toInt(fdaoChart.getFieldValue("digit"), 0);
        line.setDigit(digit);

        com.alibaba.fastjson.JSONArray arr1 = new com.alibaba.fastjson.JSONArray();
        JdbcTemplate jt = new JdbcTemplate();
        jt.setAutoClose(false);
        try {
            ResultIterator ri = jt.executeQuery(sqlData1);
            while (ri.hasNext()) {
                ResultRecord rr = ri.next();

                com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
                json.put("name", rr.getString(1));
                json.put("value", rr.getDouble(2));
                arr1.add(json);
            }
            line.setData1(arr1);

            if (!"".equals(sqlData2)) {
                com.alibaba.fastjson.JSONArray arr2 = new com.alibaba.fastjson.JSONArray();
                ri = jt.executeQuery(sqlData2);
                while (ri.hasNext()) {
                    ResultRecord rr = ri.next();

                    com.alibaba.fastjson.JSONObject json = new com.alibaba.fastjson.JSONObject();
                    json.put("name", rr.getString(1));
                    json.put("value", rr.getDouble(2));
                    arr2.add(json);
                }
                line.setData2(arr2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jt.close();
        }
        return line;
    }
}